home *** CD-ROM | disk | FTP | other *** search
/ Personal Computer World 2005 October / PCWOCT05.iso / Software / FromTheMag / XAMPP 1.4.14 / xampp-win32-1.4.14-installer.exe / xampp / php / pear / adodb / pivottable.inc.php < prev    next >
PHP Script  |  2005-01-24  |  6KB  |  185 lines
  1. <?php
  2. /** 
  3.  * @version V4.50 6 July 2004 (c) 2000-2005 John Lim (jlim@natsoft.com.my). All rights reserved.
  4.  * Released under both BSD license and Lesser GPL library license. 
  5.  * Whenever there is any discrepancy between the two licenses, 
  6.  * the BSD license will take precedence. 
  7.  *
  8.  * Set tabs to 4 for best viewing.
  9.  * 
  10.  * Latest version is available at http://php.weblogs.com
  11.  *
  12.  * Requires PHP4.01pl2 or later because it uses include_once
  13. */
  14.  
  15. /*
  16.  * Concept from daniel.lucazeau@ajornet.com. 
  17.  *
  18.  * @param db        Adodb database connection
  19.  * @param tables    List of tables to join
  20.  * @rowfields        List of fields to display on each row
  21.  * @colfield        Pivot field to slice and display in columns, if we want to calculate
  22.  *                        ranges, we pass in an array (see example2)
  23.  * @where            Where clause. Optional.
  24.  * @aggfield        This is the field to sum. Optional. 
  25.  *                        Since 2.3.1, if you can use your own aggregate function 
  26.  *                        instead of SUM, eg. $sumfield = 'AVG(fieldname)';
  27.  * @sumlabel        Prefix to display in sum columns. Optional.
  28.  * @aggfn            Aggregate function to use (could be AVG, SUM, COUNT)
  29.  * @showcount        Show count of records
  30.  *
  31.  * @returns            Sql generated
  32.  */
  33.  
  34.  function PivotTableSQL($db,$tables,$rowfields,$colfield, $where=false,
  35.      $aggfield = false,$sumlabel='Sum ',$aggfn ='SUM', $showcount = true)
  36.  {
  37.     if ($aggfield) $hidecnt = true;
  38.     else $hidecnt = false;
  39.     
  40.     $iif = strpos($db->databaseType,'access') !== false; 
  41.         // note - vfp still doesn' work even with IIF enabled || $db->databaseType == 'vfp';
  42.     
  43.     //$hidecnt = false;
  44.     
  45.      if ($where) $where = "\nWHERE $where";
  46.     if (!is_array($colfield)) $colarr = $db->GetCol("select distinct $colfield from $tables $where order by 1");
  47.     if (!$aggfield) $hidecnt = false;
  48.     
  49.     $sel = "$rowfields, ";
  50.     if (is_array($colfield)) {
  51.         foreach ($colfield as $k => $v) {
  52.             $k = trim($k);
  53.             if (!$hidecnt) {
  54.                 $sel .= $iif ? 
  55.                     "\n\t$aggfn(IIF($v,1,0)) AS \"$k\", "
  56.                     :
  57.                     "\n\t$aggfn(CASE WHEN $v THEN 1 ELSE 0 END) AS \"$k\", ";
  58.             }
  59.             if ($aggfield) {
  60.                 $sel .= $iif ?
  61.                     "\n\t$aggfn(IIF($v,$aggfield,0)) AS \"$sumlabel$k\", "
  62.                     :
  63.                     "\n\t$aggfn(CASE WHEN $v THEN $aggfield ELSE 0 END) AS \"$sumlabel$k\", ";
  64.             }
  65.         } 
  66.     } else {
  67.         foreach ($colarr as $v) {
  68.             if (!is_numeric($v)) $vq = $db->qstr($v);
  69.             else $vq = $v;
  70.             $v = trim($v);
  71.             if (strlen($v) == 0    ) $v = 'null';
  72.             if (!$hidecnt) {
  73.                 $sel .= $iif ?
  74.                     "\n\t$aggfn(IIF($colfield=$vq,1,0)) AS \"$v\", "
  75.                     :
  76.                     "\n\t$aggfn(CASE WHEN $colfield=$vq THEN 1 ELSE 0 END) AS \"$v\", ";
  77.             }
  78.             if ($aggfield) {
  79.                 if ($hidecnt) $label = $v;
  80.                 else $label = "{$v}_$aggfield";
  81.                 $sel .= $iif ?
  82.                     "\n\t$aggfn(IIF($colfield=$vq,$aggfield,0)) AS \"$label\", "
  83.                     :
  84.                     "\n\t$aggfn(CASE WHEN $colfield=$vq THEN $aggfield ELSE 0 END) AS \"$label\", ";
  85.             }
  86.         }
  87.     }
  88.     if ($aggfield && $aggfield != '1'){
  89.         $agg = "$aggfn($aggfield)";
  90.         $sel .= "\n\t$agg as \"$sumlabel$aggfield\", ";        
  91.     }
  92.     
  93.     if ($showcount)
  94.         $sel .= "\n\tSUM(1) as Total";
  95.     else
  96.         $sel = substr($sel,0,strlen($sel)-2);
  97.     
  98.     $sql = "SELECT $sel \nFROM $tables $where \nGROUP BY $rowfields";
  99.     return $sql;
  100.  }
  101.  
  102. /* EXAMPLES USING MS NORTHWIND DATABASE */
  103. if (0) {
  104.  
  105. # example1
  106. #
  107. # Query the main "product" table
  108. # Set the rows to CompanyName and QuantityPerUnit
  109. # and the columns to the Categories
  110. # and define the joins to link to lookup tables 
  111. # "categories" and "suppliers"
  112. #
  113.  
  114.  $sql = PivotTableSQL(
  115.      $gDB,                                              # adodb connection
  116.      'products p ,categories c ,suppliers s',          # tables
  117.     'CompanyName,QuantityPerUnit',                    # row fields
  118.     'CategoryName',                                    # column fields 
  119.     'p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID' # joins/where
  120. );
  121.  print "<pre>$sql";
  122.  $rs = $gDB->Execute($sql);
  123.  rs2html($rs);
  124.  
  125. /*
  126. Generated SQL:
  127.  
  128. SELECT CompanyName,QuantityPerUnit, 
  129.     SUM(CASE WHEN CategoryName='Beverages' THEN 1 ELSE 0 END) AS "Beverages", 
  130.     SUM(CASE WHEN CategoryName='Condiments' THEN 1 ELSE 0 END) AS "Condiments", 
  131.     SUM(CASE WHEN CategoryName='Confections' THEN 1 ELSE 0 END) AS "Confections", 
  132.     SUM(CASE WHEN CategoryName='Dairy Products' THEN 1 ELSE 0 END) AS "Dairy Products", 
  133.     SUM(CASE WHEN CategoryName='Grains/Cereals' THEN 1 ELSE 0 END) AS "Grains/Cereals", 
  134.     SUM(CASE WHEN CategoryName='Meat/Poultry' THEN 1 ELSE 0 END) AS "Meat/Poultry", 
  135.     SUM(CASE WHEN CategoryName='Produce' THEN 1 ELSE 0 END) AS "Produce", 
  136.     SUM(CASE WHEN CategoryName='Seafood' THEN 1 ELSE 0 END) AS "Seafood", 
  137.     SUM(1) as Total 
  138. FROM products p ,categories c ,suppliers s  WHERE p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
  139. GROUP BY CompanyName,QuantityPerUnit
  140. */
  141. //=====================================================================
  142.  
  143. # example2
  144. #
  145. # Query the main "product" table
  146. # Set the rows to CompanyName and QuantityPerUnit
  147. # and the columns to the UnitsInStock for diiferent ranges
  148. # and define the joins to link to lookup tables 
  149. # "categories" and "suppliers"
  150. #
  151.  $sql = PivotTableSQL(
  152.      $gDB,                                        # adodb connection
  153.      'products p ,categories c ,suppliers s',    # tables
  154.     'CompanyName,QuantityPerUnit',                # row fields
  155.                                                 # column ranges
  156. array(                                        
  157. ' 0 ' => 'UnitsInStock <= 0',
  158. "1 to 5" => '0 < UnitsInStock and UnitsInStock <= 5',
  159. "6 to 10" => '5 < UnitsInStock and UnitsInStock <= 10',
  160. "11 to 15"  => '10 < UnitsInStock and UnitsInStock <= 15',
  161. "16+" =>'15 < UnitsInStock'
  162. ),
  163.     ' p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID', # joins/where
  164.     'UnitsInStock',                             # sum this field
  165.     'Sum'                                        # sum label prefix
  166. );
  167.  print "<pre>$sql";
  168.  $rs = $gDB->Execute($sql);
  169.  rs2html($rs);
  170.  /*
  171.  Generated SQL:
  172.  
  173. SELECT CompanyName,QuantityPerUnit, 
  174.     SUM(CASE WHEN UnitsInStock <= 0 THEN UnitsInStock ELSE 0 END) AS "Sum  0 ", 
  175.     SUM(CASE WHEN 0 < UnitsInStock and UnitsInStock <= 5 THEN UnitsInStock ELSE 0 END) AS "Sum 1 to 5", 
  176.     SUM(CASE WHEN 5 < UnitsInStock and UnitsInStock <= 10 THEN UnitsInStock ELSE 0 END) AS "Sum 6 to 10", 
  177.     SUM(CASE WHEN 10 < UnitsInStock and UnitsInStock <= 15 THEN UnitsInStock ELSE 0 END) AS "Sum 11 to 15", 
  178.     SUM(CASE WHEN 15 < UnitsInStock THEN UnitsInStock ELSE 0 END) AS "Sum 16+",
  179.     SUM(UnitsInStock) AS "Sum UnitsInStock", 
  180.     SUM(1) as Total 
  181. FROM products p ,categories c ,suppliers s  WHERE  p.CategoryID = c.CategoryID and s.SupplierID= p.SupplierID 
  182. GROUP BY CompanyName,QuantityPerUnit
  183.  */
  184. }
  185. ?>